package me.zachwhaley.android.provider;

import me.zachwhaley.android.provider.ListContract.ListsColumns;
import me.zachwhaley.android.provider.ListContract.ThingsColumns;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

public class ListDatabase extends SQLiteOpenHelper
{

	private static final String TAG = "ListDatabase";
	private static final String DATABASE_NAME = "simplelist.db";
	public static final String SIMPLE_LISTS = "simplelists";
	
	private static final int VERSION_1 = 1;
	private static final int DATABASE_VERSION = VERSION_1;

	private static final String CREATE_LISTS_TABLE = 
	"CREATE TABLE " + Tables.LISTS + " (" + BaseColumns._ID
	+ " INTEGER PRIMARY KEY AUTOINCREMENT," 
	+ ListsColumns.LIST_NAME + " TEXT,"
	+ ListsColumns.LIST_CREATED + " DATETIME DEFAULT NOW,"
	+ ListsColumns.LIST_EDITED + " DATETIME DEFAULT NOW,"
	+ "UNIQUE (" + BaseColumns._ID + ") ON CONFLICT REPLACE);";
	
	private static final String CREATE_THINGS_TABLE = 
	"CREATE TABLE " + Tables.THINGS + " (" + BaseColumns._ID
	+ " INTEGER PRIMARY KEY AUTOINCREMENT," 
	+ ThingsColumns.THING_NAME + " TEXT," 
	+ ThingsColumns.THING_CHECK + " INTEGER DEFAULT 0,"
	+ ThingsColumns.THING_CREATED + " DATETIME DEFAULT NOW,"
	+ ThingsColumns.THING_EDITED + " DATETIME DEFAULT NOW,"
	+ ThingsColumns.LIST_ID + " INTEGER NOT NULL,"
	+ "FOREIGN KEY (" + ThingsColumns.LIST_ID + ") REFERENCES " + Tables.LISTS + " (" + ListsColumns._ID + "),"
	+ "UNIQUE (" + BaseColumns._ID + ") ON CONFLICT REPLACE);";
	
	interface Tables
	{
		public static final String LISTS = "lists";
		public static final String THINGS = "things";
		public static final String LISTS_JOIN_THINGS = "lists natural join things";
	}

	public ListDatabase(Context context)
	{
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db)
	{
		db.execSQL(CREATE_LISTS_TABLE);
		db.execSQL(CREATE_THINGS_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		Log.d(TAG, "Upgrade from " + oldVersion + " to " + newVersion);

		int version = oldVersion;
		switch (version)
		{
		case VERSION_1:
			// Use to add columns to tables
			// Ex) db.execSQL("ALTER TABLE " + Tables.Lists + " ADD COLUMN "
			// + ListsColumns.NEW_COLUMN + " TEXT");
			// version = VERSION_2;
		}
		
		Log.d(TAG, "after upgrade logic, at version " + version);
		if (version != DATABASE_VERSION)
		{
			//TODO:Make sure old data is preserved.
			Log.d(TAG, "Destroying old data during upgrade");
			
			db.execSQL("DROP TABLE IF EXISTS " + Tables.LISTS);
			db.execSQL("DROP TABLE IF EXISTS " + Tables.THINGS);
			
			onCreate(db);
		}
	}
}
